import java.sql.*;
import java.util.Scanner;

public class LIB {
    //入口函数
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //建立人程交互
        Scanner scanner = new Scanner(System.in);
        //建立驱动
        Class.forName("com.mysql.jdbc.Driver");
        //和数据库建立连接
        String url = "jdbc:mysql://127.0.0.1:3306/db_lms?useSSL=false&characterEncoding=utf-8";
        Connection connection = DriverManager.getConnection(url, "root", "123456");
        ;
        //创建好包装盒
        Statement statement = connection.createStatement();

        //登录
        System.out.println("********** 欢迎使用图书管理系统！**********");
        System.out.println("****(如果还没有账号 ，请到图书管理员处注册)***");
        System.out.println("***********（本机不支持自助注册）**********");
        boolean input = true;
        do {
            System.out.print("请输入账号：");
            String account = scanner.nextLine();
            System.out.print("请输入密码：");
            String password = scanner.nextLine();

            //拼接SQL信息
            String sql = "select * from user where account = '" + account + "' and password = '" + password + "';";

            //发送信息并执行命令
            //statement.executeQuery(sql)会依次指向表格中的每一行
            ResultSet result = statement.executeQuery(sql);


            if (result.next()) {
//                String a = "";
//                String b = "";
//                a.equals(b)
                //一样的话返回true
                input = false;

                //管理员登录
                if (account.equals("guanliyuan@qq.com")) {
                    System.out.println("管理员登录");
                    String admin_input;

                    do {

                        //管理员菜单
                        System.out.println("**********************************************");
                        System.out.println("********************用户管理********************");
                        System.out.println("* 1. 注册读者账户");
                        System.out.println("* 2. 读者账户信息修改");
                        System.out.println("* 3. 展示全部读者账户");
                        System.out.println("* 4. 排序读者账户");
                        System.out.println("* 5. 删除读者账户");
                        System.out.println("* 6. 查看近期借阅或归还记录");
                        System.out.println("**********************************************");
                        System.out.println("********************书籍管理********************");
                        System.out.println("* 7. 图书入库");
                        System.out.println("* 8. 图书出库");
                        System.out.println("* 9. 馆内图书展示");
                        System.out.println("* 10. 图书信息修改");
                        System.out.println("* 0. 退出登录");
                        System.out.println("**********************************************");
                        System.out.print("请输入要运行的操作: ");
                        admin_input = scanner.nextLine();
                        switch (admin_input) {

                            //注册读者账户
                            case "1":
                                System.out.print("请输入姓名：");
                                String addUserName = scanner.nextLine();
                                System.out.print("请输入性别：");
                                String addUserGender = scanner.nextLine();
                                System.out.print("请输入年龄：");
                                int addUserAge = Integer.parseInt(scanner.nextLine()); // Integer.valueOf() ==> 可以将字符串转换成整型
                                System.out.print("请输入账号：");
                                String addUserAccount = scanner.nextLine();
                                System.out.print("请输入密码：");
                                String addUserPassword = scanner.nextLine();

                                String addUserSQL = "insert into user values(null, '" + addUserName + "', '" + addUserGender + "', " + addUserAge + ", '" + addUserAccount + "', '" + addUserPassword + "');";

                                statement.executeUpdate(addUserSQL);
                                System.out.println("新增成功！");

                                break;


                            //读者账户信息修改
                            case "2":
                                System.out.print("请输入要修改的读者的姓名：");
                                String updateUserName = scanner.nextLine();
                                //判断名字是否存在
                                String user_sql = "select * from user where name = '" + updateUserName + "';";

                                // 执行SQL并且获取结果
                                ResultSet user_result = statement.executeQuery(user_sql);

                                //修改信息
                                if (user_result.next()) {

                                    System.out.print("请输入性别：");
                                    String updateUserGender = scanner.nextLine();
                                    System.out.print("请输入年龄：");
                                    int updateUserAge = Integer.valueOf(scanner.nextLine()); // Integer.valueOf() ==> 可以将字符串转换成整型
                                    System.out.print("请输入账号：");
                                    String updateUserAccount = scanner.nextLine();
                                    System.out.print("请输入密码：");
                                    String updateUserPassword = scanner.nextLine();

                                    String updateUserSQL = "update user set gender = '" + updateUserGender + "', age = " + updateUserAge + ", account = '" + updateUserAccount + "', password = '" + updateUserPassword + "' where name = '" + updateUserName + "';";

                                    statement.executeUpdate(updateUserSQL);
                                    System.out.println("修改成功！");

                                } else {
                                    System.out.println("姓名为【" + updateUserName + "】的用户不存在");
                                }

                                break;


                            //展示全部读者账户
                            case "3":
                                ResultSet selectUsersResult = statement.executeQuery("select * from user;");
                                System.out.println("数据库中用户信息如下：");
                                while (selectUsersResult.next()) {

                                    System.out.println("ID：" + selectUsersResult.getInt(1)
                                            + "，姓名：" + selectUsersResult.getString(2)
                                            + "，性别：" + selectUsersResult.getString(3)
                                            + "，年龄：" + selectUsersResult.getInt(4)
                                            + "，账号：" + selectUsersResult.getString(5)
                                            + "，密码：" + selectUsersResult.getString(6));
                                }
                                break;

                            //排序读者账户
                            case "4":
                                String orderUser = "select * from user order by age asc;";
                                statement.executeQuery(orderUser);
                                System.out.println("排序成功！");
                                ResultSet selectUsersResult1 = statement.executeQuery("select * from user;");
                                System.out.println("数据库中用户信息如下：");
                                while (selectUsersResult1.next()) {

                                    System.out.println("ID：" + selectUsersResult1.getInt(1)
                                            + "，姓名：" + selectUsersResult1.getString(2)
                                            + "，性别：" + selectUsersResult1.getString(3)
                                            + "，年龄：" + selectUsersResult1.getInt(4)
                                            + "，账号：" + selectUsersResult1.getString(5)
                                            + "，密码：" + selectUsersResult1.getString(6));
                                }
                                break;

                            //删除读者账户
                            case "5":
                                System.out.print("请输入要删除的读者的名字：");
                                String delete_admin = scanner.nextLine();
                                String deleteUserSQL = "delete from user where name = '" + delete_admin + "'";
                                statement.executeUpdate(deleteUserSQL);
                                System.out.println("删除成功");

                                break;

                            //查看近期借阅或归还记录
                            case "6":
                                ResultSet selectUsersResult2 = statement.executeQuery("select * from record;");
                                System.out.println("数据库中用户信息如下：");
                                while (selectUsersResult2.next()) {

                                    System.out.println("序号：" + selectUsersResult2.getInt(1)
                                            + "，用户名：" + selectUsersResult2.getString(2)
                                            + "，用户ID：" + selectUsersResult2.getInt(3)
                                            + "，书名：" + selectUsersResult2.getString(4)
                                            + "，书籍ID：" + selectUsersResult2.getInt(5)
                                            + "，操作：" + selectUsersResult2.getString(6)
                                            + "，时间：" + selectUsersResult2.getDate(7));
                                }
                                break;


                            //图书入库
                            case "7":
                                System.out.print("请输入书名：");
                                String addBookName = scanner.nextLine();
                                System.out.print("请输入作者：");
                                String addBookAuthor = scanner.nextLine();
                                System.out.print("请输入ISBN码：");
                                String addBookISBN = scanner.nextLine();
                                System.out.print("请输入页数：");
                                int addBookPages = Integer.valueOf(scanner.nextLine());
                                System.out.print("请输入价格：");
                                int addBookPrice = Integer.valueOf(scanner.nextLine());
                                System.out.print("请输入出版社：");
                                String addPUB_house = scanner.nextLine();
                                System.out.print("请输入出版日期：");
                                String addPUB_date = scanner.nextLine();
                                System.out.print("请输入是否有翻译：");
                                String addBookTransl = scanner.nextLine();
                                System.out.print("请输入书籍类型：");
                                String addBookCl = scanner.nextLine();
                                System.out.print("请输入书籍当前状态：");
                                String addBookState = scanner.nextLine();

                                String addBookSQL = "insert into book values(null, '" + addBookName + "', '" + addBookAuthor + "', '" + addBookISBN + "', "+addBookPages+", "+addBookPrice+", '" + addPUB_house + "','" + addPUB_date + "', '" + addBookTransl + "', '" + addBookCl + "', '"+addBookState+"');";

                                statement.executeUpdate(addBookSQL);
                                System.out.println("新增成功！");

                                break;

                            //图书出库
                            case "8":
                                System.out.print("请输入要删除的书的序号：");
                                String delete_admin_book = scanner.nextLine();
                                String deleteAAdminSQL = "delete from book where ordinal = '" + delete_admin_book + "';";
                                statement.executeUpdate(deleteAAdminSQL);
                                System.out.println("删除成功");

                                break;

                            //馆内图书展示
                            case "9":
                                ResultSet selectBooksResult = statement.executeQuery("select * from book;");
                                System.out.println("数据库中书籍信息如下：");
                                while (selectBooksResult.next()) {

                                    System.out.println("序号：" + selectBooksResult.getInt(1)
                                            + "，书名：" + selectBooksResult.getString(2)
                                            + "，作者：" + selectBooksResult.getString(3)
                                            + "，ISBN码：" + selectBooksResult.getString(4)
                                            + "，页数：" + selectBooksResult.getInt(5)
                                            + "，价格：" + selectBooksResult.getInt(6)
                                            + "，出版社：" + selectBooksResult.getString(7)
                                            + "，出版日期：" + selectBooksResult.getString(8)
                                            + "，是否有翻译：" + selectBooksResult.getString(9)
                                            + "，分类：" + selectBooksResult.getString(10)
                                            + "，书籍状态：" + selectBooksResult.getString(11));
                                }

                                break;

                            //图书信息修改
                            case "10":
                                System.out.print("请输入要修改的书籍的序号：");
                                String updatebookOrdinl = scanner.nextLine();
                                //判断名字是否存在
                                String admin_sql = "select * from book where ordinal = '" + updatebookOrdinl + "';";

                                // 执行SQL并且获取结果
                                ResultSet admin_result = statement.executeQuery(admin_sql);

                                //修改信息
                                if (admin_result.next()) {
                                    System.out.print("请输入书名：");
                                    String MDIBookName = scanner.nextLine();
                                    System.out.print("请输入作者：");
                                    String MDIBookAuthor = scanner.nextLine();
                                    System.out.print("请输入ISBN码：");
                                    String MDIBookISBN = scanner.nextLine();
                                    System.out.print("请输入页数：");
                                    int MDIBookPages = Integer.valueOf(scanner.nextLine());
                                    System.out.print("请输入价格：");
                                    int MDIBookPrice = Integer.valueOf(scanner.nextLine());
                                    System.out.print("请输入出版社：");
                                    String MDIPUB_house = scanner.nextLine();
                                    System.out.print("请输入出版日期：");
                                    String MDIPUB_date = scanner.nextLine();
                                    System.out.print("请输入是否有翻译：");
                                    String MDIBookTransl = scanner.nextLine();
                                    System.out.print("请输入书籍类型：");
                                    String MDIBookCl = scanner.nextLine();
                                    System.out.print("请输入书籍当前状态：");
                                    String MDIBookState = scanner.nextLine();

                                    String MDIBookSQL = "update book set name = '" + MDIBookName + "', author = '" + MDIBookAuthor + "', ISBN = '" + MDIBookISBN + "', pages = '" + MDIBookPages + "', price =  "+MDIBookPrice+"  , PUB_house = '" + MDIPUB_house + "', PUB_date = '" + MDIPUB_date + "', transl = '" + MDIBookTransl + "' , cl = '" + MDIBookCl + "', state = '" + MDIBookState + "'where ordinal = "+updatebookOrdinl+";";

                                    statement.executeUpdate(MDIBookSQL);
                                    System.out.println("修改成功！");
                                }


                                break;


                            //退出登录
                            case "0":
                                System.out.println("程序退出中……");
                                break;

                            //输入错误
                            default:
                                System.out.println("输入错误，请重新输入");
                                break;
                        }

                    } while (!admin_input.equals("0"));
                }

                //用户登录
                else {
                    System.out.println("姓名为【" + result.getString(2) + "】的用户，登录成功！");
                    String use_input;
                    do {

                        //用户菜单
                        System.out.println("**********************************************");
                        System.out.println("* 1. 个人信息修改");
                        System.out.println("* 2. 注销账户");
                        System.out.println("* 3. 借/还书");
                        System.out.println("* 4. 查看图书目录");
                        System.out.println("* 5 图书检索");
                        System.out.println("* 0. 退出登录");
                        System.out.println("**********************************************");
                        System.out.print("请输入要运行的操作: ");
                        use_input = scanner.nextLine();
                        switch (use_input) {

                            //个人信息修改
                            case "1":
                                System.out.print("请输入姓名：");
                                String updateUserName = scanner.nextLine();
                                //判断名字是否存在
                                String user_sql = "select * from user where name = '" + updateUserName + "';";

                                // 执行SQL并且获取结果
                                ResultSet user_result = statement.executeQuery(user_sql);

                                //修改信息
                                if (user_result.next()) {

                                    System.out.print("请输入性别：");
                                    String updateUserGender = scanner.nextLine();
                                    System.out.print("请输入年龄：");
                                    int updateUserAge = Integer.valueOf(scanner.nextLine()); // Integer.valueOf() ==> 可以将字符串转换成整型
                                    System.out.print("请输入账号：");
                                    String updateUserAccount = scanner.nextLine();
                                    System.out.print("请输入密码：");
                                    String updateUserPassword = scanner.nextLine();

                                    String updateUserSQL = "update user set gender = '" + updateUserGender + "', age = " + updateUserAge + ", account = '" + updateUserAccount + "', password = '" + updateUserPassword + "' where name = '" + updateUserName + "';";

                                    statement.executeUpdate(updateUserSQL);
                                    System.out.println("修改成功！");

                                } else {
                                    System.out.println("姓名为【" + updateUserName + "】的用户不存在");
                                }

                                break;


                            //注销账户
                            case "2":
                                System.out.println("请再次确认，当前操作是否为注销账户(是/否)");
                                String dest_input = scanner.nextLine();
                                String Yes = "是";
                                if (Yes.equals(dest_input)) {
                                    String deleteUserSQL = "delete from user where account = '" + account + "'";
                                    statement.executeUpdate(deleteUserSQL);
                                    System.out.println("注销成功");
                                } else {
                                    System.out.println("操作已取消");
                                }
                                break;


                            //借/还书
                            case "3":
                                ResultSet selectuserResult = statement.executeQuery("select * from user where account = '"+account+"';");

                                    int User_id = 0;
                                    String User_name = "a";
                                    //人的名字和序号
                                if (selectuserResult.next()) {

                                    User_id = selectuserResult.getInt(1);
                                    User_name = selectuserResult.getString(2);
                                }



//打印书籍表
                                ResultSet selectbooksResult = statement.executeQuery("select * from book;");
                                System.out.println("馆内书籍如下：");
                                while (selectbooksResult.next()) {

                                    //java.util.Date date = selectUsersResult.getDate(6);

                                    //java.util.Date date2 = new java.util.Date();


                                    System.out.println("序号：" + selectbooksResult.getInt(1)
                                            + "，书名：" + selectbooksResult.getString(2)
                                            + "，作者：" + selectbooksResult.getString(3)
                                            + "，ISBN码：" + selectbooksResult.getString(4)
                                            + "，页数：" + selectbooksResult.getInt(5)
                                            + "，价格：" + selectbooksResult.getInt(6)
                                            + "，出版社：" + selectbooksResult.getString(7)
                                            + "，出版日期：" + selectbooksResult.getString(8)
                                            + "，是否有翻译：" + selectbooksResult.getString(9)
                                            + "，分类：" + selectbooksResult.getString(10)
                                            + "，书籍状态：" + selectbooksResult.getString(11));
                                }
                                System.out.print("请输入要借/还的书的序号");
                                //书的序号
                                int book_borrowed_id = Integer.valueOf(scanner.next());


                                ResultSet selectbooksResult1 = statement.executeQuery("select * from book where ordinal = "+book_borrowed_id+" ;");
                                String book_name = "a";
                                if(selectbooksResult1.next())
                                {
                                    book_name = selectbooksResult1.getString(2);
                                }


                                    //书的序号



                                System.out.print("请输入你的操作：(借/还)");
                                String Operate_book = scanner.next();


                                String borrow_book = "insert into record values(null, '" + User_name + "', '"+ User_id +"', '" + book_name + "', '"+book_borrowed_id+"', '" +Operate_book+"', now());";
                                statement.executeUpdate(borrow_book);
                                System.out.println("操作成功！");

                                //如果操作是借
                                if(Operate_book.equals("借"))
                                {
                                    String book_state1 = "已借出";
                                    String book_sql = "update book set state = '"+book_state1+"' where ordinal = '"+book_borrowed_id+"';";
                                    // 执行SQL并且获取结果
                                    statement.executeUpdate(book_sql);
                                }
                                else
                                {
                                    String book_state2 = "在馆";
                                    String book_sql = "update book set state = '"+book_state2+"' where ordinal = '"+book_borrowed_id+"';";
                                    // 执行SQL并且获取结果
                                    statement.executeUpdate(book_sql);
                                }

                                break;


                                //查看图书目录
                            case "4":
                                ResultSet selectbooksResult2 = statement.executeQuery("select * from book;");
                                System.out.println("馆内书籍如下：");
                                while (selectbooksResult2.next()) {

                                    //java.util.Date date = selectUsersResult.getDate(6);

                                    //java.util.Date date2 = new java.util.Date();


                                    System.out.println("序号：" + selectbooksResult2.getInt(1)
                                            + "，书名：" + selectbooksResult2.getString(2)
                                            + "，作者：" + selectbooksResult2.getString(3)
                                            + "，ISBN码：" + selectbooksResult2.getString(4)
                                            + "，页数：" + selectbooksResult2.getInt(5)
                                            + "，价格：" + selectbooksResult2.getInt(6)
                                            + "，出版社：" + selectbooksResult2.getString(7)
                                            + "，出版日期：" + selectbooksResult2.getString(8)
                                            + "，是否有翻译：" + selectbooksResult2.getString(9)
                                            + "，分类：" + selectbooksResult2.getString(10)
                                            + "，书籍状态：" + selectbooksResult2.getString(11));
                                }
                                break;

                            //图书检索
                            case "5":
                                System.out.print("请输入想要找的书籍的类型");
                                String User_cl = scanner.next();
                                ResultSet selectbooksResult3 = statement.executeQuery("select * from book where cl = '" + User_cl + "';");
                                System.out.println("馆内书籍如下：");
                                while (selectbooksResult3.next()) {

                                    //java.util.Date date = selectUsersResult.getDate(6);

                                    //java.util.Date date2 = new java.util.Date();


                                    System.out.println("序号：" + selectbooksResult3.getInt(1)
                                            + "，书名：" + selectbooksResult3.getString(2)
                                            + "，作者：" + selectbooksResult3.getString(3)
                                            + "，ISBN码：" + selectbooksResult3.getString(4)
                                            + "，页数：" + selectbooksResult3.getInt(5)
                                            + "，价格：" + selectbooksResult3.getInt(6)
                                            + "，出版社：" + selectbooksResult3.getString(7)
                                            + "，出版日期：" + selectbooksResult3.getString(8)
                                            + "，是否有翻译：" + selectbooksResult3.getString(9)
                                            + "，分类：" + selectbooksResult3.getString(10)
                                            + "，书籍状态：" + selectbooksResult3.getString(11));
                                }
                                break;
                            //退出登录
                            case "0":
                                System.out.println("用户退出中……");
                                break;

                            //输入错误
                            default:
                                System.out.println("输入错误，请重新输入");
                                break;
                        }

                    } while (!use_input.equals("0"));
                }

            } else {
                System.out.println("账号或密码错误，请重新输入！");
            }

        } while (input);


    }
}

/*
图书管理系统
 */